package com.tianlan.blog.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.tianlan.blog.entity.SysUserRole;

import org.springframework.data.repository.query.Param;

public interface SysUserRoleDao extends JpaRepository<SysUserRole, Long>{
	
	List<SysUserRole> findByUserIdAndDelFlag(Integer userId,String delFlag);
	
	SysUserRole findOneByUserIdAndRoleIdAndDelFlag(Integer userId,Integer roleId,String delFlag);
	
	List<SysUserRole> findOneByRoleIdAndUserIdAndDelFlag(Integer roleId,Integer userId,String delFlag);

	@Transactional
	@Modifying
	@Query(value = "INSERT INTO sys_user_role(user_id,role_id,create_time,creator,update_time,operator,del_flag) "
				+ "VALUES( :#{#userRole.userId},:#{#userRole.roleId},now(),:#{#userRole.creator},now(),:#{#userRole.operator},'0' )"
	,nativeQuery = true)
	int insertUserRole(@Param("userRole") SysUserRole userRole);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE sys_user_role SET del_flag = '1' WHERE user_id = ?1"
	,nativeQuery = true)
	int deleteUserRoleByUserId(Integer userId);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE sys_user_role SET del_flag = '1' WHERE role_id = ?1"
	,nativeQuery = true)
	int deleteUserRoleByRoleId(Integer roleId);
	
	@Query(value = "SELECT count(1) from sys_user_role WHERE role_id = ?1  AND del_flag = 0 "
	,nativeQuery = true)
	int isUserRoleByRoleId(Integer roleId);
}
